<?php
/*
 * Created on Feb 5, 2009
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
class admin_Model_Service extends Clu_Db_Table_Abstract
{
	/* Update table `cities` & `districts`
	 * 
	 * Update number of places in every city & district 
	 *
	 * For example $cityRow will have $metadata
	 * 		$metadata = .. 
	 * 			'place_count' => array (
	 * 									'total' => 100000
	 * 									23 => 12324, 
	 * 									12 => 232,
	 * 									.........
	 * 			);
	 *          'event_count' => array();
	 * 
	 * 		where
	 * 			  number_of_places => Tong cong tat cac dia diem cua $row	
	 * 				 23 = category id of Restaurants,
	 * 			  12 = category of "Vietnamese Restaurants"
  	 **/
	
	public function updateNumberOfPlacesInCityAndDistrictByCategory()
	{
		$mCity = new place_Model_City();
		$mCategory = new category_Model_Category();
		$mCategory->setTables('place');
		//update number of places by city
		$select = $this->getAdapter()->select()
    								 ->from(array('c' => 'cities'),
    								 		array('c.city_id'));
    	$listCityId = $this->getAdapter()->fetchAll($select);
    	
    	foreach($listCityId as $list)
    	{					
		    $select2 = $this->getAdapter()->select()
	    								 ->from(array('p' => 'places'),
	    								 		array('count(p.city_id)', 'rtype', 'type', 'city_id')
	    								 	)
	    								 ->group(array('city_id', 'rtype', 'type'))
	    								 ->where('p.city_id = ?', $list['city_id']);
			$temp = $this->getAdapter()->fetchAll($select2);   		
	    	$city = $mCity->findByPrimary($list['city_id']);
	    	if(isset($city['metadata']))
	    	{
	    		$metadata = unserialize($city['metadata']);
//	    		$metadata = null;
	    		$metadata['place_count']['number_of_places'] = 0; 
	    	}
	    	foreach($temp as $k => $val)
	    	{
		    	if(!empty($val['rtype']))
		    	{
		    		$sql = "SELECT count(rtype) FROM places WHERE places.rtype = " . $val['rtype'] . " AND places.city_id = " . $val['city_id'];
		    		$countRtype = $this->getAdapter()->fetchAll($sql);
					$metadata['place_count'][$val['rtype']] = $countRtype['0']['count(rtype)'];
		    	}
		    	
		    	if(!empty($val['type']))
		    	{
		    		$type = explode(',', $val['type']);
		    		foreach($type as $h => $v)
		    		{
		    			$sql = "SELECT count(type) FROM places p WHERE p.type rlike '(^|,)".$v."($|,)' and p.city_id = " . $val['city_id'] ;
		    			$countType = $this->getAdapter()->fetchAll($sql);
						$metadata['place_count'][$v] = $countType['0']['count(type)'];
		    		}
		    	}
		    	$metadata['place_count']['number_of_places'] += $val['count(p.city_id)'];
	    	}
	    	$city['metadata'] = serialize($metadata);
	    	$city->save();
    	}	 
    	
    	$mDistrict = new place_Model_District();
    	//update number of places by district
    	$select1 = $this->getAdapter()->select()
    								 ->from(array('d' => 'districts'),
    								 		array('d.district_id'));
    	$listDistrictId = $this->getAdapter()->fetchAll($select1);
    	
    	foreach($listDistrictId as $list)
    	{					
		    $select3 = $this->getAdapter()->select()
	    								 ->from(array('p' => 'places'),
	    								 		array('count(p.district_id)', 'rtype', 'type', 'district_id')
	    								 	)
	    								 ->group(array('district_id', 'rtype', 'type'))
	    								 ->where('p.district_id = ?', $list['district_id'])
	    								 ;
			$temp = $this->getAdapter()->fetchAll($select3);   								 
	    	$district = $mDistrict->findByPrimary($list['district_id']);
	    	if(isset($district['metadata']))
	    	{
	    		$metadata = unserialize($district['metadata']);
    			$metadata['place_count']['number_of_places'] = 0; 
	    	}
	    	foreach($temp as $k => $val)
	    	{
		    	if(!empty($val['rtype']))
		    	{
		    		$sql = "SELECT count(rtype) FROM places WHERE places.rtype =".$val['rtype']." AND places.district_id = " . $val['district_id'];
		    		$countRtype = $this->getAdapter()->fetchAll($sql);
					$metadata['place_count'][$val['rtype']] = $countRtype['0']['count(rtype)'];
		    	}
		    	
		    	if(!empty($val['type']))
		    	{
		    		$type = explode(',', $val['type']);
		    		foreach($type as $k => $v)
		    		{
		    			$sql = "SELECT count(type) FROM places WHERE places.type rlike '(^|,)".$v."($|,)' AND places.district_id = " . $val['district_id'];
		    			$countType = $this->getAdapter()->fetchAll($sql);
						$metadata['place_count'][$v] = $countType['0']['count(type)'];
		    		}
		    	}
		    	$metadata['place_count']['number_of_places'] += $val['count(p.district_id)'];
	    	}
	    	$district['metadata'] = serialize($metadata);
	    	$district->save();
    	}	 
	}
	
	/*
	 * Ngoai so luong cua MO~I category trong 1 city/district
	 * Thi phai tinh tong so luong trong 1 category cua TOAN QUOC
	 * Va con so nay gan vao metadata cua bang "categories_place"
	 * 	$metadata = metadata cua Hotel trong ban  "categories_place"
	 * 
	 * 	$metadata = array(
	 * 			"total" => 1000  // 1000 hotels in Toan Quoc
	 * 	)
	 */
	
	public function updateTotalNumberOfPlacesInCountryForEachCategory()
	{
		$mCategory = new category_Model_Category();
		$mCategory->setTables('place');
		//update number of places by city
		$select = $this->getAdapter()->select()
		 							->from(array('p' => 'categories_place'),
	    								 		array('id'));
    	$temp = $this->getAdapter()->fetchAll($select);

    	foreach($temp as $list)
    	{					
		    $select2 = $this->getAdapter()->select()
	    								 ->from(array('c' => 'places'), array('rtype', 'type'))
									 	 ->where('country_id = 84');
									 	 
			$temp2 = $this->getAdapter()->fetchAll($select2);   
	    	$category = $mCategory->findByPrimary($list['id']);
	    	if(isset($category['metadata']))
	    		$metadata = unserialize($category['metadata']);
	    	foreach($temp2 as $k => $val)
	    	{
		    	if($val['rtype'] == $list['id'])
		    	{
		    		$sql = "SELECT count(rtype) FROM places WHERE places.rtype = " . $list['id'];
		    		$countRtype = $this->getAdapter()->fetchAll($sql);
			        if(isset($metadata[$val['rtype']]))
						$metadata['total'] = (integer) $metadata['total'] + 1;
					else
						$metadata['total'] = $countRtype['0']['count(rtype)'];
		    	}
		    	
		    	if(!empty($val['type']))
		    	{
		    		$type = explode(',', $val['type']);
		    		foreach($type as $k => $v)
		    		{
		    			if($list['id'] == $v)
		    			{
		    				$sql = "SELECT count(type) FROM places WHERE places.type = " . $list['id'];
			    			$countType = $this->getAdapter()->fetchAll($sql);
					        if(isset($metadata[$v]))
								$metadata['total'] = (integer) $metadata['total'] + 1;
							else
								$metadata['total'] = $countType['0']['count(type)'];
		    			}
		    		}
		    	}
	    	}
	    	$category['metadata'] = serialize($metadata);
	    	$category->save();
    	}	 
	}
	
	//================================
	/** update cho events city/district/categories_events */
	//================================
	
	
	
	
	/*
	 * $userRow co' $metadata
	 * 		$metadata => array(
	 * 			'post_count' => array ( 'place' => 1232,
	 * 									'event' => 123123
	 * 									'gallery' => '....',
	 * 									"blog" => "..."
	 * 		);
	 * 
	 */
	
	public function updateNumberOfContentsInUserTable()
	{
		$mUser = new user_Model_User();
		$config = array ("place" => 'places',
				"gallery" => "galleries",
				//"blog" => "nodes"
		);
		
		for($i=1; $i< 60;$i++)
		{
			if($user = $mUser->findByPrimary($i))
			{
				foreach ($config as $contentType=>$table)
				{
					$sql = "select count(id) from $table where poster_id=".$i;
					$count = $this->getAdapter()->fetchAll($sql);
					$metadata[$table] = $count['0']['count(id)'];
				}
				$user['metadata'] = serialize($metadata);
				$user->save();
			}
		}
	}
	
}
?>